package com.qf.index;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.util.NumberUtil;
import cn.hutool.db.Db;
import cn.hutool.db.Entity;
import cn.hutool.json.JSONUtil;
import com.alibaba.fastjson.JSONObject;

import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.concurrent.ConcurrentHashMap;

public class UtToBib {

    /**
     * 文献概览和预测模型，原来只可以接受pm的数据，现在修改为也可以接受WOS直接下载下来的数据，本方法只写了用户下载的数据导入到表中后的解析，以及插入到bibUtTable表中供文献概览和预测模型使用，制表符分隔文件的导入及展示导入的条数，可以参考方法：Institution
     * 贾新志 2025.03.23
     */

    public static void main(String[] args) throws SQLException {

        String newDataName = "scholar_25_02.";
        String utArticleTable = newDataName + "jining_med_coll";
        String keyWordSumTable = newDataName + "key_word_sum_0124_2";
        String jourMergeTable = newDataName + "basic_jour_merge";
        String wordAndCodeSetMapTable = newDataName + "word_and_code_set_map";
        String meshTable = newDataName + "help_mesh_tree_gcr_0615";
        String htLocationChinaTable = newDataName + "ht_location_china";
        String basicHospUnivDeptTable = newDataName + "basic_hosp_or_univ_dept";
        String bibUtTable = newDataName + "bibliometric_ut_0321";

        int onePageNum = 1000;
        String limit = "";
//        String limit = " limit 1";

        int user_id = 111;
        int sort = 1;
        String field = "UT`,`DE`,`TI`,`AB`,`J9`,`SO`,`PY`,`C1`,`RP`,`PT`,`DT`,`DI`,`VL`,`IS`,`BP`,`EP`,`SN`,`EI`,`LA`,`PM`,`WC`,`FP`,`TC`,`CR`,`ID";
        addUtBibTable(utArticleTable, onePageNum, limit, "pid", field, wordAndCodeSetMapTable, jourMergeTable,
                keyWordSumTable, meshTable, htLocationChinaTable, basicHospUnivDeptTable, bibUtTable, user_id, sort);

    }


    // 将用户下载的WOS的原始数据，转换成文献概览和预测模型可以使用的格式。本程序需要使用多张表，包括utArticleTable(此为WOS下载的解析后存表的数据)，bibUtTable（将用户存表的WOS数据保存为文献概览或预测模型可以使用的格式）
    public static void addUtBibTable(String utArticleTable, int onePageNum, String limit, String autoId, String field, String wordAndCodeSetMapTable, String jourMergeTable,
                                     String keyWordSumTable, String meshTable, String htLocationChinaTable, String basicHospUnivDeptTable, String bibUtTable, int user_id, int sort) throws SQLException {
        Map<String, En.JidInfo> zuHeAndJidInfoMap = getZuHeAndJidInfoMap(jourMergeTable, "id", "nc_nlmid_list`,`jcr_quartile`,`cite_score`,`jcr_if`,`zky_dalei`,`nc_nlm_title_abbreviation`,`ncbi_mesh`,`ncbi_subject`,`zky_subject`,`zuhe");
        Map<String, Set<Integer>> wordAndCodeSetMap = Drug.getWordAndCodeSetMap(wordAndCodeSetMapTable, "id", "word`,`code_set", "");
        Map<String, String> md5Map = new ConcurrentHashMap<>();
        Map<String, String> reKwMap = cList.getReKwMap();
        Map<String, String> keyWordAndMeshMap = new ConcurrentHashMap<>();
        Map<String, String> originalAndMeshMap = new ConcurrentHashMap<>();
        Map<String, String> lowerToUpperMap = new ConcurrentHashMap<>();
        ReadKw.readMesh(meshTable, "id", "meshName`,`entryTerms", md5Map, keyWordAndMeshMap, originalAndMeshMap, reKwMap, lowerToUpperMap);
        Set<String> originalSet = Bibliometric.getKeyWordAndStandardMap(keyWordSumTable, lowerToUpperMap, autoId, field, originalAndMeshMap);
//        Map<String, String> keyWordAndStandardMap = Bibliometric.getKeyWordAndStandardMap(keyWordSumTable, keyWordAndMeshMap, md5Map, reKwMap, "id", "is_cn`,`sum`,`key_word");
        Map<String, String> languageMap = uSet.getLanguageMap();
        Set<String> chinaSet = cList.chinaSet();
        Set<String> citySet = new HashSet<>();
        Set<String> subjectSet = cList.getSubjectSet();
        Set<String> provCitySet = Dept.getProvCitySet(htLocationChinaTable, "id", "prov`,`name", citySet);
        Map<String, String> standardMap = new ConcurrentHashMap<>();
        standardMap.put("department medical", "Department Medical");
        Map<String, Set<String>> deptAndInfoSetMap = new ConcurrentHashMap<>();
        Map<String, Integer> deptAndSumMap = new ConcurrentHashMap<>();
        Map<String, Set<String>> unitAndInfoSetMap = new ConcurrentHashMap<>();
        Map<String, Integer> unitAndSumMap = new ConcurrentHashMap<>();
        Map<String, String> countryMap = cList.getCountryMap();
        Set<String> acadSet = uSet.getAcadSet();
        Set<String> centerSet = uSet.getCenterSet();
        Set<String> collSet = uSet.getCollSet();
        Set<String> hospSet = uSet.getHospSet();
        Set<String> univSet = uSet.getUnivSet();
        Set<String> removeSet = uSet.getRemoveSet();
        Set<String> dellSet = uSet.getDellSet();
        Set<String> toDeptSet = uSet.toDept();
        Map<String, En.FuDanStemEdu> fuDanStemEduMap = Guidelines.getFuDanStemEduMap(basicHospUnivDeptTable, "id", "info`,`fudan`,`stem`,`edu");

        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        int tableMax = Db.use().queryNumber("select max(" + autoId + ") from " + utArticleTable).intValue();
        if (tableMax < 1) {
            return;
        }
        Set<String> pinYinSet = PmUnit.getPinYinSet();
        Collection<Integer> tableList = new ArrayList<>();
        NumberUtil.appendRange(0, tableMax + onePageNum, onePageNum, tableList);
        for (Integer startId : tableList) {
            List<Entity> tableData = Db.use().query("select `" + autoId + "`,`" + field + "` from " + utArticleTable + " where " + autoId + " between " + startId + " and " + (startId + onePageNum - 1) + limit);
            System.out.println("\033[31;1m" + format.format(new Date()) + " 读表现在开始：" + startId + " 表名：" + utArticleTable + Utils.printMemoryStr() + "\033[0m");
            for (Entity entry : tableData) {
                String ut = Utils.getStrField(entry, "UT").toUpperCase().replace(" ", "").replace("WOS:", "");
                String keyword = Utils.getStrField(entry, "DE");
                String title = Utils.getStrField(entry, "TI");
                String abText = Utils.getStrField(entry, "AB");
                String j9 = Utils.getStrField(entry, "J9");
                String so = Utils.getStrField(entry, "SO");
                String py = Utils.getStrField(entry, "PY");
                int year = NumberUtil.isInteger(py) ? Integer.parseInt(py.trim()) : 1900;
                List<En.C1Auth> c1List = new ArrayList<>();
                boolean isOrder = Utils.getC1ListSort(entry.getStr("C1"), pinYinSet, c1List);
                List<En.C1Auth> rpList = Utils.getRpListSort(entry.getStr("RP"), isOrder, pinYinSet);
                List<En.C1Auth> rpChangeC1 = ChangeRp.rpChangeC1(c1List, rpList);
                String pts = entry.getStr("PT");
                Set<String> ptSet = new HashSet<>(); // 出版类型
                if (null != pts && "J".equals(pts.toUpperCase().trim())) {
                    String dt = entry.getStr("DT");
                    if (null != dt && !dt.isEmpty()) {
                        String[] split = dt.split("; ");
                        for (String pt : split) {
                            if (!pt.isEmpty()) {
                                ptSet.add(pt);
                            }
                        }
                    }
                }
                String di = Utils.dellEnd(Utils.getStrField(entry, "DI"));
                String v = entry.getStr("VL");
                String i = entry.getStr("IS");
                String b = entry.getStr("BP");
                String e = entry.getStr("EP");
                String jour = Utils.toTitleCase((j9.length() > 2 && !j9.endsWith("+") ? j9 : so));
                // 格式：Anticancer Drugs. 2021 Aug 1;32(7):717-726. doi: 10.1097/CAD.0000000000001050.
                String info = jour + ". " + (year + ";") + (null == v ? "" : v) + (null == i ? "" : "(" + i + "):") + (null == b ? "" : b) + (null == e || e.isEmpty() ? ". " : "-" + e + ". ") + (di.length() > 6 ? ("doi: " + di + ". ") : "");

                String sn = Utils.getStrField(entry, "SN");
                String ei = Utils.getStrField(entry, "EI");
                En.JidInfo jidInfo = new En.JidInfo();
                if (!sn.isEmpty() && !j9.isEmpty() && zuHeAndJidInfoMap.containsKey(Institution.re("ISSN" + sn + j9))) {
                    jidInfo = zuHeAndJidInfoMap.get(Institution.re("ISSN" + sn + j9));
                } else if (!sn.isEmpty() && !so.isEmpty() && zuHeAndJidInfoMap.containsKey(Institution.re("ISSN" + sn + so))) {
                    jidInfo = zuHeAndJidInfoMap.get(Institution.re("ISSN" + sn + so));
                } else if (!ei.isEmpty() && !j9.isEmpty() && zuHeAndJidInfoMap.containsKey(Institution.re("ISSN" + ei + j9))) {
                    jidInfo = zuHeAndJidInfoMap.get(Institution.re("ISSN" + ei + j9));
                } else if (!ei.isEmpty() && !so.isEmpty() && zuHeAndJidInfoMap.containsKey(Institution.re("ISSN" + ei + so))) {
                    jidInfo = zuHeAndJidInfoMap.get(Institution.re("ISSN" + ei + so));
                }
                List<En.CategoryAndInfo> categoryAndInfoList = Drug.getDrug(title, keyword, abText, wordAndCodeSetMap);

                String language = Utils.getStrField(entry, "LA");
                String[] split = language.replace(";-;", "ĺļ").replace("; ", "ĺļ").replace(";", "ĺļ").split("ĺļ");
                List<String> languageList = new ArrayList<>();
                for (String p : split) {
                    if (p.trim().length() > 2) {
                        languageList.add(languageMap.getOrDefault(p.toLowerCase().trim(), p.trim()));
                    }
                }

                Guidelines.setC1List(c1List, standardMap, acadSet, centerSet, collSet, hospSet, univSet, removeSet, countryMap, deptAndSumMap, dellSet,
                        chinaSet, provCitySet, citySet, subjectSet, deptAndInfoSetMap, unitAndInfoSetMap, unitAndSumMap, toDeptSet, fuDanStemEduMap);

                String c1Au = JSONUtil.toJsonStr(c1List);
                List<En.SortAuthAndUnit> sortAuthList = Bibliometric.c1AuToSortAuth(c1Au);

                List<String> countryList = new ArrayList<>();
                for (En.C1Auth c1Auth : c1List) {
                    String country = c1Auth.getCountry();
                    if (country.length() > 1) {
                        countryList.add(country);
                    }
                }

                List<List<String>> kwListList = new ArrayList<>();
                if (!keyword.isEmpty()) {
                    List<String> kwList = new ArrayList<>(Arrays.asList(keyword.split("; ")));
                    ReadKw.addListList(originalAndMeshMap, kwList, kwListList, lowerToUpperMap);
                }

                List<List<String>> tiListList = new ArrayList<>();
                List<String> tiKeySet = title.length() > 4 ? ReadKw.getTiKey(title, originalSet, lowerToUpperMap) : new ArrayList<>();
                ReadKw.addListList(originalAndMeshMap, tiKeySet, tiListList, lowerToUpperMap);
                ReadKw.addAbbSet(title, tiListList, lowerToUpperMap);

                List<List<String>> abListList = new ArrayList<>();
                List<String> abKeyList = abText.length() > 10 ? ReadKw.getAbKey(abText, originalSet, lowerToUpperMap) : new ArrayList<>();
                ReadKw.addListList(originalAndMeshMap, abKeyList, abListList, lowerToUpperMap);
                ReadKw.addAbbSet(abText, abListList, lowerToUpperMap);
                En.TableData table = new En.TableData();
                table.setPmId(Utils.getStrToInt(entry, "PM"));
                table.setDi(di);
                table.setAbText(abText);
                table.setKeyword(keyword);
                table.setTitle(title);
                table.setJournal(j9);
                table.setYear(year);
                table.setIsCns(Utils.is6DaJournals("", "", so, j9));
                table.setPtSet(ptSet);
                table.setIsChina(countryList.contains("中国") ? 1 : 0);
                table.setNlmId(jidInfo.getNlmId());
                table.setCountryList(countryList);
                table.setSortAuthList(sortAuthList);
                table.setDrugGenePathwayDiseaseList(categoryAndInfoList);
                table.setInfo(Fusion.reInfo(info));
                table.setNcbiMesh(jidInfo.getNcbiMesh());
                table.setNcbiSubject(Bibliometric.strToList(jidInfo.getNcbiSubject()));
                table.setJcrIf(jidInfo.getJcrIf());
                table.setCiteScore(jidInfo.getCiteScore());
                table.setZkyDaLei(jidInfo.getZkyDaLei());
                table.setJcrQuartile(jidInfo.getJcrQuartile());
                table.setLan(CollUtil.join(languageList, "; "));
                table.setWc(Utils.getStrField(entry, "WC"));
                table.setExtractTitle(new HashSet<>(tiListList));
                table.setExtractAbstract(new HashSet<>(abListList));
                table.setKeyWordSet(new HashSet<>(kwListList));
                table.setFundList(ReadUt.getFundList(entry.getStr("FP")));
                table.setRpList(Bibliometric.c1AuToC1List(JSONUtil.toJsonStr(rpChangeC1)));
                table.setC1List(Bibliometric.c1AuToC1List(c1Au));
                table.setTotal(Utils.getStrToInt(entry, "TC"));
                table.setReferenceList(Utils.getStrToList(entry, "CR"));
                table.setKwPlusList(Utils.getStrToList(entry, "ID"));
                try {
                    Db.use().insert(Entity.create(bibUtTable).set("info", JSONUtil.toJsonStr(table)).set("ut", ut).set("user_id", user_id).set("sort", sort));
                } catch (SQLException ee) {
                    ee.printStackTrace();
                }
            }
        }
        zuHeAndJidInfoMap.clear();
        wordAndCodeSetMap.clear();
        md5Map.clear();
        reKwMap.clear();
        keyWordAndMeshMap.clear();
        originalAndMeshMap.clear();
        languageMap.clear();
        chinaSet.clear();
        citySet.clear();
        subjectSet.clear();
        provCitySet.clear();
        standardMap.clear();
        deptAndInfoSetMap.clear();
        deptAndSumMap.clear();
        unitAndInfoSetMap.clear();
        unitAndSumMap.clear();
        countryMap.clear();
        acadSet.clear();
        centerSet.clear();
        collSet.clear();
        hospSet.clear();
        univSet.clear();
        removeSet.clear();
        dellSet.clear();
        toDeptSet.clear();
        fuDanStemEduMap.clear();
    }

    public static Map<String, En.JidInfo> getZuHeAndJidInfoMap(String jourMergeTable, String autoId, String field) throws SQLException {
        int onePageNum = 1000;
        Map<String, En.JidInfo> zuHeAndJidInfoMap = new HashMap<>();
        int tableMax = Db.use().queryNumber("select max(" + autoId + ") from " + jourMergeTable).intValue();
        if (tableMax > 0) {
            Collection<Integer> tableList = new ArrayList<>();
            NumberUtil.appendRange(0, tableMax + onePageNum, onePageNum, tableList);
            for (Integer startId : tableList) {
                List<Entity> tableData = Db.use().query("select `" + autoId + "`,`" + field + "` from " + jourMergeTable + " where " + autoId + " between " + startId + " and " + (startId + onePageNum - 1));
                for (Entity entry : tableData) {
                    En.JidInfo jidInfo = new En.JidInfo();
                    String nlmIds = Utils.getStrField(entry, "nc_nlmid_list");
                    if (nlmIds.contains("\"]")) {
                        List<String> nlmIdList = JSONObject.parseArray(nlmIds, String.class);
                        if (!nlmIdList.isEmpty()) {
                            jidInfo.setNlmId(nlmIdList.getFirst().toUpperCase().trim());
                            jidInfo.setNcbiJournal(Utils.getStrField(entry, "nc_nlm_title_abbreviation").replace("\"", ""));
                            String meshs = Utils.getStrField(entry, "ncbi_mesh").replace("\"", "");
                            if (!meshs.isEmpty()) {
                                List<String> list = Arrays.asList(meshs.split("Łł"));
                                jidInfo.setNcbiMesh(CollUtil.join(list, "; "));
                            }
                            String subjects = Utils.getStrField(entry, "ncbi_subject");
                            if (subjects.contains("\"]")) {
                                List<String> list = JSONObject.parseArray(subjects, String.class);
                                jidInfo.setNcbiSubject(CollUtil.join(list, "; ").replace("\"", ""));
                            }
                        }
                    }
                    int jid = Utils.getIntField(entry, autoId);
                    jidInfo.setJid(jid);
                    String jcrQuartile = Utils.getStrField(entry, "jcr_quartile").toUpperCase().replace("Q", "").replace(" ", "").trim();
                    jidInfo.setJcrQuartile(NumberUtil.isInteger(jcrQuartile) ? Integer.parseInt(jcrQuartile) : 0);
                    jidInfo.setZkySubject(Utils.getStrField(entry, "zky_subject"));
                    jidInfo.setCiteScore(Utils.getDouField(entry, "cite_score"));
                    jidInfo.setJcrIf(Utils.getDouField(entry, "jcr_if"));
                    jidInfo.setZkyDaLei(Utils.getIntField(entry, "zky_dalei"));
                    String zuHes = Utils.getStrField(entry, "zuhe");
                    if (zuHes.contains("\"]")) {
                        List<String> zuHeList = JSONObject.parseArray(zuHes, String.class);
                        for (String zuHe : zuHeList) {
                            if (zuHe.contains("ISSN")) {
                                zuHeAndJidInfoMap.put(Institution.re(zuHe), jidInfo);
                            }
                        }
                    }
                }
            }
        }
        return zuHeAndJidInfoMap;
    }


}
